import pandas as pd
import json
from datetime import datetime

def extract_production_data(excel_path):
    """
    从Excel文件中提取生产线数据
    
    参数:
        excel_path: Excel文件路径
        
    返回:
        包含生产线数据的JSON对象
    """

    # 加载 Excel 文件
    df = pd.read_excel(excel_path, sheet_name=0, header=None)
    
    # 找到 "Production Line" 所在的单元格位置，并确认其右侧单元格为 "Project"
    target_cell = "Production Line"
    adjacent_cell = "Project"
    start_row, start_col = None, None
    
    for row in range(df.shape[0]):
        for col in range(df.shape[1]):
            # 检查当前单元格是否为目标单元格
            if df.iat[row, col] == target_cell:
                # 检查右侧单元格是否为预期值
                if col + 1 < df.shape[1] and df.iat[row, col + 1] == adjacent_cell:
                    start_row, start_col = row, col
                    break
        if start_row is not None:
            break
    
    if start_row is None:
        return {"error": "未找到包含 'Production Line' 且右侧为 'Project' 的单元格。"}
    
    # 提取从该单元格开始的表格
    data_section = df.iloc[start_row:, start_col:]
    
    # 将第一行作为列名
    data_section.columns = data_section.iloc[0]
    data_section = data_section[1:].reset_index(drop=True)
    
    # 获取日期信息所在行（在"Planned"单元格上面两格）
    date_row = start_row - 2
    
    result = []
    
    # 查找包含"Part Number"的列名，避免处理换行符
    part_number_col = None
    for col in data_section.columns:
        if isinstance(col, str) and "Part Number" in col:
            part_number_col = col
            break
    
    # 查找所有包含"Planned"的列及其索引
    planned_cols_indices = []
    for i, col in enumerate(data_section.columns):
        if isinstance(col, str) and "Planned" in col:
            planned_cols_indices.append((i, col))
    
    # 第一个"Planned"列是每周计划，其余是每日计划
    weekly_planned_idx, weekly_planned_col = planned_cols_indices[0] if planned_cols_indices else (None, None)
    daily_planned_indices = [(idx, col) for idx, col in planned_cols_indices[1:]] if len(planned_cols_indices) > 1 else []
    
    for idx, row in data_section.iterrows():
        # 跳过空行
        if isinstance(row["Production Line"], float) and pd.isna(row["Production Line"]):
            continue
            
        production_line = ""
        if not pd.isna(row["Production Line"]):
            production_line = str(row["Production Line"]).strip()
        
        # 处理项目名称，如果有总项目名称，则组合
        project = ""
        if "Project" in row and not pd.isna(row["Project"]):
            project = str(row["Project"]).strip()

        
        # 处理编号，列名包括换行符，此处做特殊处理
        part_number = ""
        if part_number_col and part_number_col in row and not pd.isna(row[part_number_col]):
            part_number = str(row[part_number_col]).strip()
        
        designation = ""
        if "Designation" in row and not pd.isna(row["Designation"]):
            designation = str(row["Designation"]).strip()
        
        # 获取每周计划
        weekly_plan = row.iloc[weekly_planned_idx] if weekly_planned_idx is not None else None
                        
        if isinstance(weekly_plan, float) and pd.isna(weekly_plan):
            weekly_plan = 0
        elif isinstance(weekly_plan, str):
            weekly_plan = weekly_plan.strip()
            try:
                weekly_plan = int(weekly_plan)
            except (ValueError, TypeError):
                pass
        else:
            try:
                weekly_plan = int(weekly_plan)
            except (ValueError, TypeError):
                weekly_plan = str(weekly_plan).strip()
        
        # 获取每日计划
        daily_plans = {}
        for col_idx, _ in daily_planned_indices:
            # 获取当前列在原始DataFrame中的索引
            original_col_idx = start_col + col_idx
            
            # 获取对应的日期
            date_str = None
            if date_row >= 0:
                date_cell = df.iat[date_row, original_col_idx]
                if isinstance(date_cell, datetime):
                    date_str = date_cell.strftime("%Y/%m/%d")
                elif not pd.isna(date_cell):
                    date_str = str(date_cell).strip()
            
            # 每日计划值
            value = row.iloc[col_idx]
            
            if pd.isna(value):
                value = 0
            elif isinstance(value, str):
                value = value.strip()
                try:
                    value = int(value)
                except (ValueError, TypeError):
                    pass
            else:
                try:
                    value = int(value)
                except (ValueError, TypeError):
                    if not isinstance(value, (int, float)):
                        value = 0
            
            daily_plans[date_str] = value
        
        line_data = {
            "Production Line": production_line,
            "Project": project,
            "Part Number/Train NO": part_number,
            "Designation": designation,
            "Weekly Plan": weekly_plan,
            "Daily Plans": daily_plans
        }
        
        result.append(line_data)
    
    return result


if __name__ == "__main__":
    # file_path = "../frontend/Bonding_Painting.xlsx"
    file_path = "/Users/jinyifeng/Desktop/workspace/wabtec-console_1stbondingpainting/frontend/2025WK42-Bonding and Painting.xlsx"
    data = extract_production_data(file_path)
    
    # 输出JSON格式的结果
    print(json.dumps(data, ensure_ascii=False, indent=2))
